{% extends "base/base.html" %}
{% load  asset_filter %}
{% load i18n %}
{% load  bootstrap3 %}
{% load  static %}


{% block header-css %}

  <link href="/static/css/plugins/toastr/toastr.min.css" rel="stylesheet">


{% endblock %}

{% block  title %}SQL{% endblock %}



{% block page-content %}


    <div class="wrapper wrapper-content animated fadeInRight">
        <div class="row">
            <div class="col-lg-4" id="split-right">

                <div class="ibox float-e-margins">

                    <div class="ibox-title">
                        <h5>SQL</h5>
                        <div class="ibox-tools">

                        </div>
                    </div>
                    <div class="ibox-content">
                        <form enctype="multipart/form-data" id="ddl" method="post" class="form-horizontal"
                              action="">


                            <div class="form-group">
                                <div class="col-sm-10 col-sm-offset-0">

                                    {% bootstrap_field form.region layout="horizontal" %}
                                    {% bootstrap_field form.name layout="horizontal" %}
                                    {% bootstrap_field form.data_base layout="horizontal" %}
                                    <div class="form-group">
                                        <label class="col-md-3 control-label"></label>

                                        <div class="col-md-9">

                                            <div class="help-block">* 必填</div>
                                        </div>
                                    </div>
                                    {% bootstrap_field form.table layout="horizontal" %}
                                    {#                                     {% bootstrap_field form.ps layout="horizontal" %}#}
                                    {#                                                                        <div class="form-group">#}
                                    {#                                        <label class="col-md-3 control-label"></label>#}
                                    {##}
                                    {#                                        <div class="col-md-9">#}
                                    {##}
                                    {#                                            <div class="help-block">* 必填</div>#}
                                    {#                                        </div>#}
                                    {#                                    </div>#}
                                    {% bootstrap_field form.backup layout="horizontal" %}

                                </div>
                            </div>

                            <div class="hr-line-dashed"></div>


                            <div class="form-group">
                                <div class="col-sm-10 col-sm-offset-0">
                                    <div class="col-md-3 control-label"></div>

                                    <a class="btn btn-primary   structure  ">
                                        获取表结构信息
                                    </a>

                                    <a class="btn btn-danger reset">重置</a>
                                </div>
                            </div>


                        </form>

                    </div>
                </div>
            </div>
            <div class="col-lg-8" id="split-right">


                <div class="tabs-container">
                    <ul class="nav nav-tabs">
                        <li class="active"><a data-toggle="tab" href="#tab-1"><i class="fa fa-code"></i> 填写SQL语句 </a>
                        </li>
                        <li class=""><a data-toggle="tab" href="#tab-2"> <i class="fa fa-folder"></i>表结构详情
                        </a></li>
                        <li class=""><a data-toggle="tab" href="#tab-3"> <i class="fa fa-folder-o"></i>索引详情 </a></li>
                    </ul>
                    <div class="tab-content">
                        <div id="tab-1" class="tab-pane active">
                            <div class="panel-body">

                                <pre id="sql_content_editor" class="ace_editor " style="min-height:350px"></pre>
                                <div class="hr-line-dashed"></div>

                                <form class="form-horizontal" method="post">

                                    <table class="table table-striped table-bordered table-hover "
                                           style="width: 100%;"
                                           id="table3">
                                        <thead>
                                        <tr>
                                                                           <th>ID</th>
                                                <th>阶段</th>
                                                <th>错误等级</th>
                                                <th>阶段状态</th>
                                                <th>错误信息 </th>
                                                <th>当前检查的sql </th>
                                                <th>预计影响的SQL  </th>
                                        </tr>


                                        </thead>

                                        <tbody>


                                        </tbody>


                                    </table>


                                </form>

                                <div class="hr-line-dashed"></div>


                                <div class="form-group">
                                    <div class="col-sm-12 ">

                                        <a class="btn btn-success  sql_optimize  ">
                                            格式美化
                                        </a>
                                        <a class="btn btn-warning  sql_advice  ">
                                            语法检查
                                        </a>
                                         <a class="btn btn-primary  sql_test  ">
                                            SQL检测
                                        </a>
                                        <a class="btn btn-danger  sql_exe">
                                            SQL执行
                                        </a>

                                    </div>
                                </div>
                                <br/>
                                <div class="hr-line-dashed"></div>


                                <div class="form-group">
                                    <pre class="sql_advice_out">## 格式美化: 对SQL进行格式化！
## 语法检查: 包含SQL语法检查以及优化建议！
## SQL检测: 对SQL进行预执行检测, 检测无报错后，才可以 SQL执行！
## SQL执行: 执行SQL语句，检测失败则不进行执行。请修改后进行检测，通过后再执行！</pre>
                                </div>


                            </div>


                        </div>

                        <div id="tab-2" class="tab-pane">

                            <div class="panel-body">
                                <div class="table-responsive">

                                    <form class="form-horizontal" method="post">

                                        <table class="table table-striped table-bordered table-hover "
                                               style="width: 100%;"
                                               id="table1">
                                            <thead>
                                            <tr>
                                                <th>字段名</th>
                                                <th>字段类型</th>
                                                <th>字段是否为空</th>
                                                <th>默认值</th>
                                                <th>备注</th>
                                            </tr>


                                            </thead>
                                            <tbody>


                                            </tbody>


                                        </table>


                                    </form>
                                </div>


                            </div>


                        </div>
                        <div id="tab-3" class="tab-pane">
                            <div class="panel-body">
                                <div class="table-responsive">


                                    <form class="form-horizontal" method="post">

                                        <table class="table table-striped table-bordered table-hover "
                                               style="width: 100%;"
                                               id="table2">
                                            <thead>
                                            <tr>
                                                <th>索引名称</th>
                                                <th>是否唯一索引</th>
                                                <th>字段名</th>
                                            </tr>


                                            </thead>
                                            <tbody>


                                            </tbody>


                                        </table>


                                    </form>


                                </div>
                            </div>
                        </div>

                    </div>


                </div>

            </div>
        </div>
    </div>


    <div class="select-wrapper" style="display:none;">

        <select id="language">
            <option value="sql">
                SQL
            </option>

        </select>
    </div>


    {% block footer-js %}

        <script type="text/javascript" src="/static/js/sql-formatter.min.js"></script>
 <script src="/static/js/plugins/toastr/toastr.min.js"></script>

        <!-- CodeMirror -->
        <script src="{% static 'ace/ace.js' %}"></script>
        <script src="{% static 'ace/ext-language_tools.js' %}"></script>
        <script src="{% static 'ace/mode-mysql.js' %}"></script>
        <script src="{% static 'ace/theme-github.js' %}"></script>
        <script src="{% static 'ace/snippets/mysql.js' %}"></script>
        <script src="{% static 'ace/ace-init.js' %}"></script>


        <script type="text/javascript">
        toastr.options = {
          "closeButton": true,
          "debug": false,
          "progressBar": true,
          "preventDuplicates": false,
          "positionClass": "toast-top-right",
          "onclick": null,
          "showDuration": "500",
          "hideDuration": "1000",
          "timeOut": "7000",
          "extendedTimeOut": "1000",
          "showEasing": "swing",
          "hideEasing": "linear",
          "showMethod": "fadeIn",
          "hideMethod": "fadeOut"
        }
        $(".sql_exe").attr({"disabled":"disabled"});

            var input_list = [];

            var editor = ace.edit("sql_content_editor", {
                enableLiveAutocompletion: true,
                enableBasicAutocompletion: true,
                enableSnippets: true,
            });
            editor.getSession().setMode("ace/mode/mysql");
            editor.setFontSize(16);

            var languageTools = ace.require("ace/ext/language_tools");
            languageTools.addCompleter({
                getCompletions: function (editor, session, pos, prefix, callback) {
                    callback(null, input_list
                    );
                }
            });


            {#格式美化  #}
            $(document).on('click', '.sql_optimize', function () {
                    let sqlContent = "";
                    var select_sqlContent = editor.session.getTextRange(editor.getSelectionRange());
                    if (select_sqlContent) {
                        sqlContent = select_sqlContent
                    } else {
                        sqlContent = editor.getValue();
                    }
                    sqlContent = window.sqlFormatter.format(sqlContent);
                    editor.setValue(sqlContent);
                    editor.clearSelection();

                }
            );


            {#SQL优化建议 #}
            $(document).on('click', '.sql_advice', function () {

                $.ajax({
                    url: "{% url 'sql:sql_query'  pk='advice'  %}",
                    data: {'sql': editor.getValue()},
                    type: 'POST',
                    success: function (data) {
                        var obj = JSON.parse(data);
                        $(".sql_advice_out").html(obj['data'])
                    }
                });
            });

           $(document).on('click', '.sql_test', function () {
                var Name = $('#id_name').val();
                 var backup = $('input[name="backup"]:checked').val();
                 if (backup == "on"){
                     backup = 1
                 }else{
                     backup = 0
                 }
                $.ajax({
                    url: "{% url 'sql:sql_query'  pk='sql_test'  %}?name=" + Name+"&backup="+backup,
                    data: {'sql': editor.getValue()},
                    type: 'POST',
                    success: function (data) {
                        var obj = JSON.parse(data);
                        if (obj['error'] == 0){
                            $(".sql_exe").removeAttr("disabled");
                        }
                        $("#table3").dataTable().fnDestroy();
                       $('#table3').DataTable({
                        data: obj['data'],
                        columns: [
                            {data: 'order_id'},
                            {data: 'stage'},
                            {data: 'error_level'},
                            {data: 'stage_status'},
                            {data: 'error_message'},
                            {data: 'sql'},
                            {data: 'affected_rows'}
                        ],

                         "oLanguage": {
                            "sLengthMenu": "每页显示 _MENU_ 条记录",
                            "sZeroRecords": "对不起，查询不到任何相关数据",
                            "sInfo": "当前显示 _START_ 到 _END_ 条，共 _TOTAL_条记录",
                            "sInfoEmtpy": "找不到相关数据",
                            "sInfoFiltered": " 数据表中共为 _MAX_ 条记录",
                            "sProcessing": "正在加载中...",
                            "sSearch": "搜索",
                            "oPaginate": {
                                "sFirst": "第一页",
                                "sPrevious": " 上一页 ",
                                "sNext": " 下一页 ",
                                "sLast": " 最后一页 "
                            }
                        },
                        dom: '<html5>',
                        "ordering": false, // 禁止排序
                        "info": false,
                        "bPaginate": false
                    });
                    }
                });
            });

           $(document).on('click', '.sql_exe', function () {
                var Name = $('#id_name').val();
                 var backup = $('input[name="backup"]:checked').val();
                 if (backup == "on"){
                     backup = 1
                 }else{
                     backup = 0
                 }
                $.ajax({
                    url: "{% url 'sql:sql_query'  pk='sql_exe'  %}?name=" + Name+"&backup="+backup,
                    data: {'sql': editor.getValue()},
                    type: 'POST',
                    success: function (data) {
                        var obj = JSON.parse(data);
                        if (obj['error']){
                            toastr.error(obj['error'])
                        }

                        $("#table3").dataTable().fnDestroy();
                        $('#table3').DataTable({
                        data: obj['data'],
                        columns: [
                            {data: 'order_id'},
                            {data: 'stage'},
                            {data: 'error_level'},
                            {data: 'stage_status'},
                            {data: 'error_message'},
                            {data: 'sql'},
                            {data: 'affected_rows'}
                        ],

                         "oLanguage": {
                            "sLengthMenu": "每页显示 _MENU_ 条记录",
                            "sZeroRecords": "对不起，查询不到任何相关数据",
                            "sInfo": "当前显示 _START_ 到 _END_ 条，共 _TOTAL_条记录",
                            "sInfoEmtpy": "找不到相关数据",
                            "sInfoFiltered": " 数据表中共为 _MAX_ 条记录",
                            "sProcessing": "正在加载中...",
                            "sSearch": "搜索",
                            "oPaginate": {
                                "sFirst": "第一页",
                                "sPrevious": " 上一页 ",
                                "sNext": " 下一页 ",
                                "sLast": " 最后一页 "
                            }
                        },
                        dom: '<html5>',
                        "ordering": false, // 禁止排序
                        "info": false,
                        "bPaginate": false
                    });
                    }
                });
            });

            window.onload = function () {
                $(".sql").addClass("active");
                $(".sql_ddl").addClass("active");
                $(".sql").children('ul').addClass('in');
                $(".sql_ddl").children('ul').addClass('in');
            };


            $('#id_region').change(function () {
                var Region = $('#id_region').val();
                $.getJSON("{% url 'sql:sql_query'  pk='name'  %}?region=" + Region, function (data, textStatus) {
                    var content = '<option>' + '</option>';
                    $.each(data, function (i, item) {
                        for (i = 0; i < item.length; i++) {
                            content += '<option   value=' + item[i] + '>' + item[i] + '</option>'
                        }
                    });
                    $('#id_name').html(content)
                });
            });
            $('#id_name').change(function () {
                var Region = $('#id_region').val();
                var Name = $('#id_name').val();
                $.getJSON("{% url 'sql:sql_query'  pk='databases'  %}?region=" + Region + "&name=" + Name, function (data, textStatus) {
                    var content = '<option>' + '</option>';
                    $.each(data, function (i, item) {
                        for (i = 0; i < item.length; i++) {
                            content += '<option   value=' + item[i] + '>' + item[i] + '</option>'
                        }
                    });
                    $('#id_data_base').html(content)
                });
            });

            $('#id_data_base').change(function () {
                var Region = $('#id_region').val();
                var Name = $('#id_name').val();
                var DataBase = $('#id_data_base').val();
                input_list = [];
                $.getJSON("{% url 'sql:sql_query'  pk='tables'  %}?region=" + Region + "&name=" + Name + "&data_base=" + DataBase, function (data, textStatus) {
                    var content = '<option>' + '</option>';
                    $.each(data, function (i, item) {
                        for (i = 0; i < item.length; i++) {
                            content += '<option   value=' + item[i] + '>' + item[i] + '</option>';
                            input_list.push({
                                name: item[i],
                                value: item[i],
                                caption: item[i],
                                meta: item[i],
                                type: "local",
                                score: i + 900
                            },)
                        }
                    });
                    input_list.push({
                        name: DataBase,
                        value: DataBase,
                        caption: DataBase,
                        meta: DataBase,
                        type: "local",
                        score: 900
                    });
                    $('#id_table').html(content)
                });
            });


            $(document).on('click', '.reset', function () {
                $("#id_region").val(null).trigger("change");
                $("#id_name").val(null).trigger("change");
                $("#id_data_base").val(null).trigger("change");
                $("#id_table").val(null).trigger("change");

            });

            $(document).on('click', '.structure', function () {
                var Region = $('#id_region').val();
                var Name = $('#id_name').val();
                var DataBase = $('#id_data_base').val();
                var Table = $('#id_table').val();
                $.getJSON("{% url 'sql:sql_query'  pk='structure'  %}?region=" + Region + "&name=" + Name + "&data_base=" + DataBase + "&table=" + Table, function (data, textStatus) {


                    $("#table1").dataTable().fnDestroy();
                    $("#table2").dataTable().fnDestroy();
                    var data1 = data['data']['field'];

                    $.each(data1, function (i, item) {
                        input_list.push({
                            name: item["Field"],
                            value: item["Field"],
                            caption: item["Field"],
                            meta: item["Field"],
                            type: "local",
                            score: i + 900
                        },)
                    });


                    var data2 = data['data']['idx'];

                    $('#table1').DataTable({
                        data: data1,
                        columns: [
                            {data: 'Field'},
                            {data: 'Type'},
                            {data: 'Null'},
                            {data: 'Key'},
                            {data: 'Default'}
                        ],

                        "oLanguage": {
                            "sLengthMenu": "每页显示 _MENU_ 条记录",
                            "sZeroRecords": "对不起，查询不到任何相关数据",
                            "sInfo": "当前显示 _START_ 到 _END_ 条，共 _TOTAL_条记录",
                            "sInfoEmtpy": "找不到相关数据",
                            "sInfoFiltered": " 数据表中共为 _MAX_ 条记录",
                            "sProcessing": "正在加载中...",
                            "sSearch": "搜索",
                            "oPaginate": {
                                "sFirst": "第一页",
                                "sPrevious": " 上一页 ",
                                "sNext": " 下一页 ",
                                "sLast": " 最后一页 "
                            }
                        },
                        dom: '<"html5buttons"B>lTfgitp,',
                        {#"order": [[0, 'desc']],#}
                        "ordering": false, // 禁止排序
                        buttons: ['copy', 'csv', 'excel']
                    });
                    $('#table2').DataTable({
                        data: data2,
                        columns: [
                            {data: 'key_name'},
                            {data: 'Non_unique'},
                            {data: 'column_name'}
                        ],

                        "oLanguage": {
                            "sLengthMenu": "每页显示 _MENU_ 条记录",
                            "sZeroRecords": "对不起，查询不到任何相关数据",
                            "sInfo": "当前显示 _START_ 到 _END_ 条，共 _TOTAL_条记录",
                            "sInfoEmtpy": "找不到相关数据",
                            "sInfoFiltered": " 数据表中共为 _MAX_ 条记录",
                            "sProcessing": "正在加载中...",
                            "sSearch": "搜索",
                            "oPaginate": {
                                "sFirst": "第一页",
                                "sPrevious": " 上一页 ",
                                "sNext": " 下一页 ",
                                "sLast": " 最后一页 "
                            }
                        },
                        dom: '<html5>',
                        "ordering": false, // 禁止排序
                        "info": false,
                        "bPaginate": false
                    });
                });


            })




        </script>




    {% endblock %}

{% endblock %}